#!/usr/bin/perl -w


use strict;
use warnings;
use utf8;
use open ':encoding(utf8)';
use Algorithm::Diff;
use Unicode::Normalize qw(decompose NFC);
use Data::Dumper;
use lib "../";
use UnboundHelpers;
#require '../UnboundHelpers.pm';

use vars qw($dbh);
require "../../conn.pl";

use XML::LibXML;

my $dir = __FILE__; $dir =~ s/\.pl$//;

open IN, "$dir/kjv_apocrypha_utf8.txt"
	or die "Dir kjv_apocrypha_utf8.txt doesn't exist.";


#TODO: We need to add triggers so that when an object is deleted, it's corresponding TEXT entries are deleted as well

#Add record to Work
my $url = 'http://www.unboundbible.org/downloads/bibles/kjv_apocrypha.zip'; #this is the same as the URL in externals.txt
print "Deleting existing work...\n";
my $workSTH = $dbh->prepare("SELECT id FROM work WHERE url = ?");
$workSTH->execute($url);
if($workSTH){
	my $workHash = $workSTH->fetchrow_hashref;
	if($workHash){
		$dbh->do("DELETE FROM ref WHERE type = 'verse' AND work = " . $workHash->{id});
		$dbh->do("DELETE FROM ref WHERE type = 'chapter' AND work = " . $workHash->{id});
		$dbh->do("DELETE FROM ref WHERE type = 'book' AND work = " . $workHash->{id});
		$dbh->do("DELETE FROM token WHERE work = " . $workHash->{id});
		$dbh->do("DELETE FROM token_parsing WHERE work = " . $workHash->{id});
		$dbh->do("DELETE FROM work WHERE id = " . $workHash->{id});
	}
}


print "Inserting work...\n";
$dbh->do("INSERT INTO work ( name, abbreviation, type, copyright, url ) VALUES(
							 INSERT_TEXT(NULL, 'en', ?, NULL),
							 INSERT_TEXT(NULL, 'en', ?, NULL),
							 ?,
							 INSERT_TEXT(NULL, 'en', ?, NULL),
							 ?
							)", undef,
						(
							"King James Version",
							"KJV", #OSIS code
							'translation',
							"Imported from University of Pennsylvania CCAT Biblical text archive.",
							$url
						)
);
my $workID = $dbh->last_insert_id(undef, undef, undef, undef);

#Add license
print "Assigning license...\n";
my $licenseSTH = $dbh->prepare("SELECT * FROM license WHERE url = ?");
$licenseSTH->execute('http://creativecommons.org/licenses/publicdomain/');
my $licenseID = $licenseSTH->fetchrow_hashref->{id};
$dbh->do("INSERT INTO work_license ( work, license ) VALUES(?, ?)", undef, (
	$workID, $licenseID
));

#Prepare refs
my($bookID, $chapterID, $verseID);
my($book, $lastBook, $chapter, $lastChapter, $verse, $lastVerse) = (-1, -1, -1, -1, -1, -1);

my $insertToken = $dbh->prepare('INSERT INTO token (data, type, position, work) VALUES(?,?,?,?)');
my $insertTokenParsing = $dbh->prepare('INSERT INTO token_parsing (token, parse, strongs, lemma, work) VALUES(?,?,?,?,?)');

#my $insertBookGroup = $dbh->prepare("INSERT INTO ref (type, start_token, numerical_start, name) VALUES ('bookGroup', ?, ?, INSERT_TEXT(NULL, 'en', ?, NULL))");
my $insertBook = $dbh->prepare("INSERT INTO ref (type, work, start_token, numerical_start, name) VALUES ('book', $workID, ?, ?, INSERT_TEXT(NULL, 'en', ?, NULL))");
my $insertChapter = $dbh->prepare("INSERT INTO ref (type, work, start_token, numerical_start, parent) VALUES ('chapter', $workID, ?, ?, ?)");
my $insertVerse = $dbh->prepare("INSERT INTO ref (type, work, start_token, numerical_start, parent) VALUES ('verse', $workID, ?, ?, ?)");

print "Inserting tokens...\n";

my $position = 0;
while(<IN>){
	next if !/^62N\t/; #is 1 John
	s/^(\d+)(\w)\t+(\d+)\t+(\d+)\t+\d+\t+//;
	
	my $group = $2;
	my $book = $1;
	my $chapter = $3;
	my $verse = $4;
	#next if $chapter > 2; #skip if after chapter 2
	
	chomp;
	s/\r//g;
	
	#formatUnboundSelectingVariant(1, $book, $chapter, $verse);
	my @words = UnboundHelpers::formatUnboundSelectingVariant(1, $book, $chapter, $verse);
	chomp $_;
	my $refSaved = 0;
	
	foreach my $word (@words){ #(split(/\r?\n/)){
		++$position;
		$insertToken->execute(NFC($word->{word}), 'word', $position, $workID);
		my $tokenID = $dbh->last_insert_id(undef, undef, undef, undef);
		
		delete $word->{word};
		
		#$insertTokenParsing->execute($tokenID, NFC(to_json($word)), $workID);
		$insertTokenParsing->execute(
			$tokenID,
			NFC($word->{parse}),
			NFC(join(';', @{$word->{strongs}})),
			undef,
			$workID
		);
		
		if(!$refSaved){
			
			if($book != $lastBook){
				$insertBook->execute($tokenID, $book, "1 John");
				$bookID = $dbh->last_insert_id(undef, undef, undef, undef);
			}
			if($chapter != $lastChapter){
				$insertChapter->execute($tokenID, $chapter, $bookID);
				$chapterID = $dbh->last_insert_id(undef, undef, undef, undef);
			}
			if($verse != $lastVerse){
				$insertVerse->execute($tokenID, $verse, $chapterID);
				$verseID = $dbh->last_insert_id(undef, undef, undef, undef);
			}
			$refSaved = 1;
		}
	}
	$lastChapter = $chapter;
	$lastBook = $book;
	$lastVerse = $verse;
}
close IN;
print "done\n";


1;